﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using DBUtlity;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class UserTeamRoleService
    {
        /// <summary>
        /// 查询数据库中是否有该用户名
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public int GetUserName(string userName)
        {
            string sql = "select count(*) from UserInfoes where UserName=@UserName";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserName", userName)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 新增一条用户信息
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="displayName"></param>
        /// <param name="email"></param>
        /// <returns></returns>
        public int InsertUserInfo(string userName, string displayName, string email)
        {
            string sql = "insert into UserInfoes(UserName,DisplayName,Email) values(@UserName,@DisplayName,@Email)";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserName",userName),
                new SqlParameter("DisplayName",displayName),
                new SqlParameter("@Email",email)
            };
            return Convert.ToInt32(SQLHelper.Update(sql, param));
        }
        /// <summary>
        /// 查询用户所在的所有团队
        /// </summary>
        /// <param name="userName"></param>
        /// <returns></returns>
        public List<UserTeamRole> GetUserTeam(string userName)
        {
            string sql = "select UserName,UserTeamRoles.TeamId,TeamName from UserTeamRoles ";
            sql += "inner join Teams on Teams.TeamId=UserTeamRoles.TeamId ";
            sql += "where UserName=@UserName";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserName",userName)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            List<UserTeamRole> userList = new List<UserTeamRole>();
            while (objReader.Read())
            {
                userList.Add(new UserTeamRole()
                {
                    UserName = objReader["UserName"].ToString(),
                    TeamId = Convert.ToInt32(objReader["TeamId"]),
                    TeamName = objReader["TeamName"].ToString()
                });
            }
            objReader.Close();
            return userList.Where((x, i) => userList.FindIndex(z => z.TeamName == x.TeamName) == i).ToList();
        }
        /// <summary>
        /// 根据团队、角色、工作流ID查询用户
        /// </summary>
        /// <param name="teamId"></param>
        /// <param name="roleId"></param>
        /// <param name="workMouldId"></param>
        /// <returns></returns>
        public List<UserInfo> GetUserInfo(int teamId, int roleId, int workMouldId)
        {
            string sql = "select UserInfoes.UserName,DisplayName,Email from UserInfoes ";
            sql += "inner join UserTeamRoles on UserInfoes.UserName=UserTeamRoles.UserName ";
            sql += "where TeamId=@TeamId and RoleId=@RoleId and WorkMouldId=@WorkMouldId ";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@TeamId",teamId),
                new SqlParameter("@RoleId",roleId),
                new SqlParameter("@WorkMouldId",workMouldId)
            };
            SqlDataReader objReader = SQLHelper.GetReader(sql, param);
            List<UserInfo> list = new List<UserInfo>();
            while (objReader.Read())
            {
                list.Add(new UserInfo()
                {
                    UserName = objReader["UserName"].ToString(),
                    DisplayName = objReader["DisplayName"].ToString(),
                    Email = objReader["Email"].ToString()
                });
            }
            objReader.Close();
            return list;
        }
        /// <summary>
        /// 组合查询权限
        /// 需要参数：工作流名称或用户姓名
        /// 返回结果：姓名 团队 角色 工作流名称列表
        /// </summary>
        /// <param name="workflowName"></param>
        /// <param name="displayName"></param>
        /// <returns></returns>
        public List<UserTeamRole> GetUserInfo(string workflowName, string displayName)
        {
            List<SqlParameter> paramList = new List<SqlParameter>();
            string sql = "select UserTeamRoles.Id,UserTeamRoles.UserName,UserTeamRoles.TeamId,";
            sql += "UserTeamRoles.RoleId,UserTeamRoles.WorkMouldId,RoleName,TeamName,DisplayName,";
            sql += "WorkName from UserTeamRoles ";
            sql += "inner join UserInfoes on UserTeamRoles.UserName=UserInfoes.UserName ";
            sql += "inner join Roles on UserTeamRoles.RoleId=Roles.RoleId ";
            sql += "inner join Teams on UserTeamRoles.TeamId=Teams.TeamId ";
            sql += "inner join WorkflowMoulds on UserTeamRoles.WorkMouldId=WorkflowMoulds.WorkParentId ";
            sql += "where 1=1";
            if(!String.IsNullOrEmpty(workflowName))
            {
                sql += " and WorkName like '%'+@WorkName+'%'";
                paramList.Add(new SqlParameter("@WorkName", workflowName));
            }
            if (!String.IsNullOrEmpty(displayName))
            {
                sql += " and DisplayName=@DisplayName";
                paramList.Add(new SqlParameter("@DisplayName", displayName));
            }
            SqlDataReader objReader = SQLHelper.GetReader(sql, paramList.ToArray());
            List<UserTeamRole> list = new List<UserTeamRole>();
            while(objReader.Read())
            {
                list.Add(new UserTeamRole()
                {
                    Id = Convert.ToInt32(objReader["Id"]),
                    UserName = objReader["UserName"].ToString(),
                    Displayname = objReader["DisplayName"].ToString(),
                    TeamId = Convert.ToInt32(objReader["TeamId"]),
                    TeamName = objReader["TeamName"].ToString(),
                    RoleId = Convert.ToInt32(objReader["RoleId"]),
                    RoleName = objReader["RoleName"].ToString(),
                    WorkMouldId = Convert.ToInt32(objReader["WorkMouldId"]),
                    WorkName = objReader["WorkName"].ToString()
                });
            }
            return list;
        }
        /// <summary>
        /// 组合查询权限
        /// 需要参数：工作流模板Id 用户名 角色Id
        /// 返回结果：符合条件的数量
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="roleId"></param>
        /// <param name="workMouldId"></param>
        /// <returns></returns>
        public int GetUserInfo(string userName,int roleId,int workMouldId)
        {
            string sql = "select count(*) from UserTeamRoles where UserName=@Username";
            sql += " and RoleId=@RoleId and WorkMouldId=@WorkMouldId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserName",userName),
                new SqlParameter("@RoleId",roleId),
                new SqlParameter("@WorkMouldId",workMouldId)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
        /// <summary>
        /// 组合查询权限
        /// 需要参数：工作流模板Id 用户名 团队Id
        /// 返回结果：符合条件的数量
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="roleId"></param>
        /// <param name="workMouldId"></param>
        /// <returns></returns>
        public int GetUserInfoTeam(string userName, int teamId, int workMouldId)
        {
            string sql = "select count(*) from UserTeamRoles where UserName=@Username";
            sql += " and TeamId=@TeamId and WorkMouldId=@WorkMouldId";
            SqlParameter[] param = new SqlParameter[]
            {
                new SqlParameter("@UserName",userName),
                new SqlParameter("@TeamId",teamId),
                new SqlParameter("@WorkMouldId",workMouldId)
            };
            return Convert.ToInt32(SQLHelper.GetSingleResult(sql, param));
        }
    }
}
